【数据篇】SpringBoot 整合 MyBatis

2023-05-12 19:56| 来源: 网络整理| 查看: 265

写在最前

本文在【数据篇】SpringBoot 整合 MyBatis-Plus 增强 MyBatis ，基友搭配，效率翻倍基础上使用 MyBatis-Plus 实现分页查询。

构建查询数据 -- 创建岗位信息表 CREATE TABLE `sys_post` ( `post_id` bigint NOT NULL AUTO_INCREMENT COMMENT '岗位ID', `post_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '岗位编码', `post_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '岗位名称', `post_sort` int NOT NULL COMMENT '岗位排序', `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '是否删除 -1：已删除 0：正常', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '创建人', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT '更新人', `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注信息', PRIMARY KEY (`post_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='岗位信息表'; -- 向员工表插入数据 INSERT INTO `sys_post` VALUES (1, 'user', '员工', 2, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (2, 'cto', 'cto', 0, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '秃头大佬'); INSERT INTO `sys_post` VALUES (3, 'user', '董事长', -1, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '资本家'); INSERT INTO `sys_post` VALUES (4, 'user1', '员工1', 3, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (5, 'user2', '员工2', 4, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (6, 'user3', '员工3', 5, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (7, 'user4', '员工4', 6, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (8, 'user5', '员工5', 7, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (9, 'user6', '员工6', 8, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (10, 'user7', '员工7', 9, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); INSERT INTO `sys_post` VALUES (11, 'user8', '员工8', 10, '0', '2023-03-16 13:14:20', 'admin', '2023-06-26 13:14:20', 'admin', '打工人'); Mybatis-Plus Page

该类继承了 IPage 类，实现了简单分页模型如果你要实现自己的分页模型可以继承 Page 类或者实现 IPage 类

属性名类型默认值描述recordsListemptyList查询数据列表totalLong0查询列表总记录数sizeLong10每页显示条数，默认 10currentLong1当前页ordersListemptyList排序字段信息，允许前端传入的时候，注意 SQL 注入问题，可以使用 SqlInjectionUtils.check(...) 检查文本optimizeCountSqlbooleantrue自动优化 COUNT SQL 如果遇到 jSqlParser 无法解析情况，设置该参数为 falseoptimizeJoinOfCountSqlbooleantrue自动优化 COUNT SQL 是否把 join 查询部分移除searchCountbooleantrue是否进行 count 查询，如果指向查询到列表不要查询总记录数，设置该参数为 falsemaxLimitLong单页分页条数限制countIdStringxml 自定义 count 查询的 statementId 基于 Wrapper 分页查询

Demo 地址：mingyue-springboot-mybatis-plus

1. 添加岗位实体 import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import io.swagger.v3.oas.annotations.media.Schema; import lombok.Data; import lombok.EqualsAndHashCode; /** * 岗位管理 * * @author Strive * @date 2022-03-15 17:18:40 */ @Data @TableName("sys_post") @EqualsAndHashCode(callSuper = true) @Schema(description = "岗位信息表") public class MingYuePost extends BaseEntity { private static final long serialVersionUID = -8744622014102311894L; /** * 岗位ID */ @TableId(type = IdType.ASSIGN_ID) @Schema(description = "岗位ID") private Long postId; /** * 岗位编码 */ @Schema(description = "岗位编码") private String postCode; /** * 岗位名称 */ @Schema(description = "岗位名称") private String postName; /** * 岗位排序 */ @Schema(description = "岗位排序") private Integer postSort; /** * 是否删除 -1：已删除 0：正常 */ @Schema(description = "是否删除 -1：已删除 0：正常") private String delFlag; /** * 备注信息 */ @Schema(description = "备注信息") private String remark; } 2. 添加岗位 Mapper import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.csp.mingyue.mybatisPlus.model.MingYuePost; import org.apache.ibatis.annotations.Mapper; /** * @author Strive */ @Mapper public interface MingYuePostMapper extends BaseMapper { } 3. 添加岗位 Service import cn.hutool.core.util.StrUtil; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.csp.mingyue.mybatisPlus.mapper.MingYuePostMapper; import com.csp.mingyue.mybatisPlus.model.MingYuePost; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Service; /** * @author Strive * @date 2023/5/12 10:22 */ @Slf4j @Service @RequiredArgsConstructor public class MingYuePostService extends ServiceImpl { } 4. 提供分页查询接口 import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.csp.mingyue.mybatisPlus.model.MingYuePost; import com.csp.mingyue.mybatisPlus.service.MingYuePostService; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.RequiredArgsConstructor; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; /** * @author Strive * @date 2023/5/12 10:21 */ @Api(tags = "岗位模块") @RestController @RequiredArgsConstructor @RequestMapping("/post") public class MingYuePostController { private final MingYuePostService mingYuePostService; @ApiOperation("根据用户ID查询用户信息") @GetMapping("/page") public ResponseEntity page(Page page) { return ResponseEntity.ok(mingYuePostService.page(page)); } } 5. 新增分页拦截器 import com.baomidou.mybatisplus.annotation.DbType; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; /** * @author Strive * @date 2023/5/12 15:51 */ @Configuration public class MyBatisPlusConfig { /** * 分页插件 */ @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } } 6. 测试接口

http://127.0.0.1:8080/post/page?size=5¤t=1&orders%5B0%5D.column=post_sort&orders%5B0%5D.asc=true

执行 SQL 如下：

SELECT post_id, post_code, post_name, post_sort, del_flag, remark, create_by, create_time, update_by, update_time FROM sys_post ORDER BY post_sort ASC LIMIT 5

返回数据如下：

{ "records": [ { "createBy": "admin", "createTime": "2023-03-16T13:14:20", "updateBy": "admin", "updateTime": "2023-06-26T13:14:20", "postId": 3, "postCode": "boss", "postName": "董事长", "postSort": -1, "delFlag": "0", "remark": "资本家" }, { "createBy": "admin", "createTime": "2023-03-16T13:14:20", "updateBy": "admin", "updateTime": "2023-06-26T13:14:20", "postId": 2, "postCode": "cto", "postName": "cto", "postSort": 0, "delFlag": "0", "remark": "秃头大佬" }, { "createBy": "admin", "createTime": "2023-03-16T13:14:20", "updateBy": "admin", "updateTime": "2023-06-26T13:14:20", "postId": 1, "postCode": "user", "postName": "员工", "postSort": 2, "delFlag": "0", "remark": "打工人" }, ... ], "total": 11, "size": 5, "current": 1, "orders": [ { "column": "post_sort", "asc": true } ], "optimizeCountSql": true, "searchCount": true, "countId": null, "maxLimit": null, "pages": 3 } 基于 SQL 分页查询 1. 添加分页查询 Mapper /** * 分页查询岗位信息 * @param page 分页参数 * @param mingYuePost * @return */ IPage selectPageSql(@Param("page") Page page,@Param("query") MingYuePost mingYuePost); 2. 编写 Mapper.xml SELECT p.post_id, p.post_name, p.post_code, p.post_sort as postSort, p.del_flag, p.create_time, p.update_time, p.update_by, p.create_by, p.remark FROM sys_post p p.del_flag = '0' and p.post_name LIKE #{postNameLike} ORDER BY ${order.column} asc desc 3. 编写 Service public IPage pageSql(Page page, MingYuePost mingYuePost) { return mingYuePostMapper.selectPageSql(page, mingYuePost); } 4. 编写接口 @ApiOperation("分页查询岗位信息(SQl)") @GetMapping("/pageSql") public ResponseEntity pageSql(Page page, MingYuePost mingYuePost) { return ResponseEntity.ok(mingYuePostService.pageSql(page, mingYuePost)); } 5. 测试接口

http://127.0.0.1:8080/post/pageSql?postName=%E5%91%98%E5%B7%A5&orders%5B0%5D.column=postSort&orders%5B0%5D.asc=false&size=5¤t=1

执行 SQL 如下：

SELECT p.post_id, p.post_name, p.post_code, p.post_sort AS postSort, p.del_flag, p.create_time, p.update_time, p.update_by, p.create_by, p.remark FROM sys_post p WHERE p.del_flag = '0' AND p.post_name LIKE '%员工%' ORDER BY postSort DESC, postSort DESC LIMIT 5

返回数据如下：

{ "records": [ { "createBy": "admin", "createTime": "2023-03-16T13:14:20", "updateBy": "admin", "updateTime": "2023-06-26T13:14:20", "postId": 11, "postCode": "user8", "postName": "员工8", "postSort": 10, "delFlag": "0", "remark": "打工人" }, { "createBy": "admin", "createTime": "2023-03-16T13:14:20", "updateBy": "admin", "updateTime": "2023-06-26T13:14:20", "postId": 10, "postCode": "user7", "postName": "员工7", "postSort": 9, "delFlag": "0", "remark": "打工人" }, { "createBy": "admin", "createTime": "2023-03-16T13:14:20", "updateBy": "admin", "updateTime": "2023-06-26T13:14:20", "postId": 9, "postCode": "user6", "postName": "员工6", "postSort": 8, "delFlag": "0", "remark": "打工人" }, ... ], "total": 0, "size": 10, "current": 1, "orders": [ { "column": "postSort", "asc": false } ], "optimizeCountSql": true, "searchCount": true, "countId": null, "maxLimit": null, "pages": 0 }

【本文地址】

公司简介

联系我们